JOIN an Access table and a SQL Server table in a single query from Excel VBA

I am using Excel 64-bit

I have a ms-access database and in this database I have normal ms-access table and some linked table from SQL Server, I have a query that is taking a reference of a linked table, and when I am firing that query from excel vba it is giving me an ODBC error, however I am successfully able to fetch non linked table from excel vba.

Now I am thinking about different approach, can it be possible to join ms-access and SQL Server table in a single query, I found some code from net and tried my luck, but it is not working and giving an error message "Could not find installable ISAM", below is the code that I am using.

Note:- table "PeopleMain" is a sql server table, and except this table all are ms-access table.

[code]

  Sub FetchData3()

Dim rs As Object
Dim cn As Object
Dim ss As String
Dim conn As String
Dim accdb As Object


conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\Workflow Tools (Michael Cantor)\Tool For Fixing Bug From Michael Cantor\PI MDT Reconciliation Workflow Tool\PI Database.accdb;Persist Security Info=False;Mode=Read"

 ss = "SELECT RM.ReconciliationID, RM.FirmID, RM.FirmName,  RM.DateRequested, RM.DueDate, Rm.ExtendedDueDate, " & _
    "Requestor.Name, SecondaryRequestor.Name FROM " & _
    "((ReconciliationMaster RM INNER JOIN Reconciliation_Fund RF ON RF.ReconciliationID = RM.ReconciliationID) " & _
    "LEFT JOIN (SELECT Preferred_Name + ' ' + Last_Name AS Name, People_ID FROM " & _
    "[Provider=sqloledb;Server=servername;Database=database;Trusted_Connection=Yes].PeopleMain) Requestor  " & _
    "ON Requestor.People_ID = RM.PrimaryRequestor) LEFT JOIN (SELECT Preferred_Name + ' ' + Last_Name AS Name, " & _
    "People_ID FROM [Provider=sqloledb;Server=servername;Database=database;Trusted_Connection=Yes].PeopleMain) " & _
    "SecondaryRequestor ON SecondaryRequestor.People_ID = RM.SecondaryRequestor WHERE RM.ReconciliationID = 522;"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open conn

rs.Open ss, cn


Sheet1.Cells.ClearContents
Sheet1.Range("A1").CopyFromRecordset rs

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

MsgBox "done"

End Sub

Thanks Kashif